package com.mmy.dao.line;

import java.util.Date;
import java.util.List;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import com.mmy.domain.line.VLineOrder;

public interface VLineOrderRepository extends JpaRepository<VLineOrder, Integer> {

	/**
	 * 根据线路Id查询对应订单
	 *
	 * @author 创建人 liys
	 * @date 创建日期 2020.12.25
	 */
	@Query(value = "select * from vLineOrder where iLineID = ?1", nativeQuery = true)
	List<VLineOrder> findByLineID(int iLineID);

	/**
	 * 根据Id查询对应订单
	 *
	 * @author 创建人 liys
	 * @date 创建日期 2021.01.25
	 */
	@Query(value = "select * from vLineOrder where iID = ?1", nativeQuery = true)
	VLineOrder findVByID(int iID);

	/**
	 * 查询所有订单
	 *
	 * @author 创建人 liys
	 * @date 创建日期 2020.12.25
	 */
	@Query(value = "select * from vLineOrder where iType = :iType", nativeQuery = true)
	List<VLineOrder> findAll(@Param("iType") int iType);

	/**
	 * 去付款页， 根据预留姓名、时间查询 订单组
	 *
	 * @author 创建人 liys
	 * @date 创建日期 2021.03.22
	 */
	@Query(value = "select * from vLineOrder where iType = :iType and dReserveDate = :reserveDate and cReserveName = :strReserveName", nativeQuery = true)
	List<VLineOrder> searchReserveGroup(@Param("strReserveName") String strReserveName,
			@Param("reserveDate") Date reserveDate, @Param("iType") int iType);

	/**
	 * 预留
	 *
	 * @author 创建人 liys
	 * @date 创建日期 2021.03.22
	 */
	@Query(value = "select * from vLineOrder where iOrderState = 0 and iType = :iType "
			+ "and (iLineOrgID = :iOrgID or iReserveUserOrgID = :iOrgID) "
			+ "and (:Org1 = \"\" or (iLineOrgID = :Org1 and (iReserveUserType = 0 or (iReserveUserType = 1 and iReserveUserOrgID = :Org1 ))))"
			+ "and (:Org2 = \"\" or (iLineOrgID <> :Org2 and iReserveUserOrgID = :Org2))"
			+ "and (:Org3 = \"\" or (iReserveUserType = 1 and iReserveUserOrgID <> :Org3 and iLineOrgID = :Org3))"
			+ "and (:lineName = \"\" or cLineName like %:lineName%)"
			+ "and (:strReserveName = \"\" or cReserveName like %:strReserveName%)"
			+ "and (:strReserveTel = \"\" or cReserveTel like %:strReserveTel%) "
			+ "and (:strReserveDay = \"\" or to_days(dReserveDate) = to_days(:strReserveDay))"
			+ "and iID in (select any_value(iID) from vLineOrder GROUP BY cReserveName having min(cOrderID)) ORDER BY dReserveDate desc limit :iTempPageNo,:iPageSize", nativeQuery = true)
	List<VLineOrder> reserved(@Param("Org1") String Org1, @Param("Org2") String Org2, @Param("Org3") String Org3,
			@Param("iOrgID") int iOrgID, @Param("lineName") String lineName,
			@Param("strReserveName") String strReserveName, @Param("strReserveTel") String strReserveTel,
			@Param("strReserveDay") String strReserveDay, @Param("iTempPageNo") int iTempPageNo,
			@Param("iPageSize") int iPageSize, @Param("iType") int iType);

	/**
	 * 订单
	 *
	 * @author 创建人 liys
	 * @date 创建日期 2021.03.22
	 */
	@Query(value = "select * from vLineOrder where iType = :iType and (iOrderState = 1 or iOrderState = 2) "
			+ "and (iLineOrgID = :iOrgID or iOrderUserOrgID = :iOrgID) "
			+ "and (:Org1 = \"\" or (iLineOrgID = :Org1 and iOrderUserOrgID = :Org1))"
			+ "and (:Org2 = \"\" or (iLineOrgID <> :Org2 and iOrderUserOrgID = :Org2))"
			+ "and (:Org3 = \"\" or (iLineOrgID = :Org3 and iOrderUserOrgID <> :Org3))"
			+ "and (:lineName = \"\" or cLineName like %:lineName%)"
			+ "and (:strOrderName = \"\" or cOrderName like %:strOrderName%)"
			+ "and (:strOrderTel = \"\" or cOrderTel like %:strOrderTel%) "
			+ "and (:strOrderDay = \"\" or to_days(dOrderDate) = to_days(:strOrderDay))"
			+ "ORDER BY dOrderDate desc limit :iTempPageNo,:iPageSize", nativeQuery = true)
	List<VLineOrder> paid(@Param("Org1") String Org1, @Param("Org2") String Org2, @Param("Org3") String Org3,
			@Param("iOrgID") int iOrgID, @Param("lineName") String lineName, @Param("strOrderName") String strOrderName,
			@Param("strOrderTel") String strOrderTel, @Param("strOrderDay") String strOrderDay,
			@Param("iTempPageNo") int iTempPageNo, @Param("iPageSize") int iPageSize, @Param("iType") int iType);

	/**
	 * 退单
	 *
	 * @author 创建人 liys
	 * @date 创建日期 2021.02.01
	 */
	@Query(value = "select * from vLineOrder where iType = :iType and iRefundProcess <> 0 "
			+ "and (iLineOrgID = :iOrgID or iOrderUserOrgID = :iOrgID) "
			+ "and (:Org1 = \"\" or (iLineOrgID = :Org1 and iOrderUserOrgID = :Org1))"
			+ "and (:Org2 = \"\" or (iLineOrgID <> :Org2 and iOrderUserOrgID = :Org2))"
			+ "and (:Org3 = \"\" or (iLineOrgID = :Org3 and iOrderUserOrgID <> :Org3))"
			+ "and (:lineName = \"\" or cLineName like %:lineName%)"
			+ "and (:strOrderName = \"\" or cOrderName like %:strOrderName%)"
			+ "and (:strOrderTel = \"\" or cOrderTel like %:strOrderTel%) "
			+ "and (:strOrderDay = \"\" or to_days(dOrderDate) = to_days(:strOrderDay))"
			+ "ORDER BY dOrderDate desc limit :iTempPageNo,:iPageSize", nativeQuery = true)
	List<VLineOrder> cancel(@Param("Org1") String Org1, @Param("Org2") String Org2, @Param("Org3") String Org3,
			@Param("iOrgID") int iOrgID, @Param("lineName") String lineName, @Param("strOrderName") String strOrderName,
			@Param("strOrderTel") String strOrderTel, @Param("strOrderDay") String strOrderDay,
			@Param("iTempPageNo") int iTempPageNo, @Param("iPageSize") int iPageSize, @Param("iType") int iType);

	/**
	 * 分页参数-预留
	 *
	 * @author 创建人 liys
	 * @date 创建日期 2021.01.25
	 */
	@Query(value = "select count(*) from vLineOrder where iType = :iType and (iLineOrgID = :iOrgID or iReserveUserOrgID = :iOrgID) and iOrderState = 0", nativeQuery = true)
	int reservedCount(@Param("iOrgID") int iOrgID, @Param("iType") int iType);

	/**
	 * 分页参数-订单
	 *
	 * @author 创建人 liys
	 * @date 创建日期 2020.12.31
	 */
	@Query(value = "select count(*) from vLineOrder where iType = :iType and (iOrderUserOrgID = :iOrgID or iLineOrgID = :iOrgID) and (iOrderState = 1 or iOrderState = 2)", nativeQuery = true)
	int paidCount(@Param("iOrgID") int iOrgID, @Param("iType") int iType);

	/**
	 * 分页参数-退单
	 *
	 * @author 创建人 liys
	 * @date 创建日期 2021.01.26
	 */
	@Query(value = "select count(*) from vLineOrder where iType = :iType and (iOrderUserOrgID = :iOrgID or iLineOrgID = :iOrgID) and iRefundProcess <> 0 ", nativeQuery = true)
	int cancelCount(@Param("iOrgID") int iOrgID, @Param("iType") int iType);

	/*
	 * ===========================================会计================================
	 * =============
	 */

	/**
	 * 本公司--订单
	 *
	 * @author 创建人 liys
	 * @date 创建日期 2021.01.26
	 */
	@Query(value = "select * from vLineOrder where iType = 1 and iOrderUserID = :iOrgID and iOrderState = 2 ", nativeQuery = true)
	List<VLineOrder> findOrderPaid(@Param("iOrgID") int iOrgID);

	/**
	 * 线路--本公司--退款单
	 *
	 * @author 创建人 liys
	 * @date 创建日期 2021.01.26
	 */
	@Query(value = "select * from vLineOrder where iType = 1 and iOrderUserID = :iOrgID and iOrderState = 4", nativeQuery = true)
	List<VLineOrder> findOrderCancel(@Param("iOrgID") int iOrgID);

}
